Introdução

Os dados utilizados nessa análise são provenientes do banco de dados do TCE/PB, mais específicamente as bases de dados cabobranco e vigia, as quais são utilizadas na aplicação GEO Obras.

Como existem 46 tabelas nesse banco de dados, analisaremos apenas as que estão bastante atreladas as obras, visto que neste banco de dados existem também outros dados necessários para a aplicação rodar. Com isso, analisaremos a tabela obra, as que são referenciadas por esta, bem como a tabela de acompanhamento.

Tabela obras

Vejamos a tabela de obras:

obra %>% 
    str()
## 'data.frame':    28681 obs. of  16 variables:
##  $ id                          : num  1 2 3 4 5 7 8 12 13 14 ...
##  $ fk_jurisdicionado           : num  634 587 587 794 802 796 631 749 684 668 ...
##  $ numero_contrato             : chr  "402012016" "000522016" "000532016" "000992016" ...
##  $ numero_obra                 : chr  "00022016" "00012016" "00022016" "00022016" ...
##  $ descricao_sucinta_obra      : chr  "Construção de muro de contorno da Escola Francisco Chaves Ventura no Município de Camalaú." "REFORMA E AMPLIAÇÃO DA ESCOLA LÍDIA CABRAL DE SOUSA AGUIAR PB" "CONTINUIDADE DA CONSTRUÇÃO DE UM CAMPO DE FUTEBOL" "AMPLIAÇÃO DA ESCOLA PADRE IBIAPINA – SANTA FÉ – SOLÂNEA," ...
##  $ fk_localidade               : num  1341 1293 1293 1384 1338 ...
##  $ descricao_localidade        : chr  "Rua Projetada, s/n, Centro, Camalaú-PB" "RUA SEVERINO AMÂNCIO, S/N, NO MUNICÍPIO DE AGUIAR" "RUA SEVERINO AMÂNCIO, S/N, NO MUNICÍPIO DE AGUIAR PB" "SANTA FÉ - SOLÂNEA" ...
##  $ fk_tipo_obra                : int  24 24 24 24 1 1 24 1 10 1 ...
##  $ fk_tipo_execucao            : chr  "[1]" "[2,3]" "[1]" "[2]" ...
##  $ valor_obra                  : num  146656 358546 314988 139158 127169 ...
##  $ planilha_contratada         : chr  "Proposta Caipira - Contorno do Muro.xlsx" "cmd.exe" "cmd.exe" "ESCOLA SANTA FÉ - BM1_.xls" ...
##  $ obra_incorporavel_patrimonio: logi  TRUE TRUE TRUE TRUE TRUE TRUE ...
##  $ dimensao                    : num  374 532 440 213 1 ...
##  $ numero_protocolo_tramita    : chr  "49496/16" "49641/16" "49653/16" "49810/16" ...
##  $ fk_complexo_obras           : num  1 2 3 4 5 7 8 12 13 14 ...
##  $ cancelled                   : logi  FALSE FALSE FALSE FALSE FALSE FALSE ...

Na tabela de obras existem 16 colunas, onde temos:

  • id : O próprio id da tabela
  • fk_jurisdicionado : uma chave estrangeira para a tabela jurisdicionado
  • numero_contrato : o número do contrato da obra
  • numero_obra : o número da obra
  • descricao_sucinta_obra : a descrição sucinta da obra
  • fk_localidade : uma chave estrangeira para a tabela de localidade
  • descricao_localidade : a descrição da localidade
  • fk_tipo_obra : uma chave estrangeira para o tipo da obra
  • fk_tipo_execucao : uma chave estrangeira para o tipo de execução
  • valor_obra : o valor da obra
  • planilha_contratada : uma referência para a planilha da obra?
  • obra_incorporavel_patrimonio: um boolean que indica se a obra é incorporável ao patrimônio
  • dimensao : a dimensão da obra
  • numero_protocolo_tramita : o número do protocolo tramita
  • fk_complexo_obras : uma chave estrangeira para o complexo da obra
  • cancelled : um boolean que indica se a obra foi cancelada ou não

Chave estrangeira jurisdicionado

Como falado acima, existe uma chave estrangeira para o jurisdicionado, mas a tabela de jurisdicionado do db vigia é vazia.

jurisdicionado_db1 %>% count()
## # A tibble: 0 x 0

Com isso, ela provavelmente se refere à tabela do db cabobranco, visto que existem observações no segundo.

jurisdicionado_db2 %>% count()
## # A tibble: 1 x 1
##       n
##   <int>
## 1  1048

Chaves estrangeiras desconhecidas

A chave estrangeira fk_tipo_obra não é encontrada em nenhuma outra tabela, assim como fk_tipo_execucao. Apesar de existir uma coluna planilha_contratada na tabela obras, não foi encontrada outra tabela que contenha estas planilhas.

Chave estrangeira localidade

Vejamos a tabela de localidade, referente a chave estrangeira fk_localidade da tabela obras:

localidade %>% 
    str()
## 'data.frame':    5566 obs. of  16 variables:
##  $ id                : num  1313 1293 1390 1391 1398 ...
##  $ uf                : chr  "Paraíba" "Paraíba" "Paraíba" "Paraíba" ...
##  $ codigo_uf         : int  25 25 25 25 25 25 25 25 25 25 ...
##  $ mesoregiao        : chr  "Sertão Paraibano" "Sertão Paraibano" "Agreste Paraibano" "Agreste Paraibano" ...
##  $ codigo_mesoregiao : int  2501 2501 2503 2503 2503 2502 2503 2504 2503 2503 ...
##  $ microregiao       : chr  "Serra do Teixeira" "Piancó" "Brejo Paraibano" "Brejo Paraibano" ...
##  $ codigo_microregiao: int  25007 25005 25015 25015 25016 25011 25012 25023 25016 25012 ...
##  $ nome              : chr  "Água Branca" "Aguiar" "Alagoa Grande" "Alagoa Nova" ...
##  $ codigo_ibge       : int  2500106 2500205 2500304 2500403 2500502 2500536 2500577 2500601 2500809 2500908 ...
##  $ codigo_siaf       : int  0 981903 981905 981907 981909 980440 980442 981911 981915 981917 ...
##  $ link_ibge         : chr  "http://cidades.ibge.gov.br/xtras/perfil.php?codmun=2500106" "http://cidades.ibge.gov.br/xtras/perfil.php?codmun=2500205" "http://cidades.ibge.gov.br/xtras/perfil.php?codmun=2500304" "http://cidades.ibge.gov.br/xtras/perfil.php?codmun=2500403" ...
##  $ bandeira          : chr  "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ ...
##  $ brasao            : chr  "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ "\\xffd8ffe000104a46494600010200000100010000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f"| __truncated__ ...
##  $ link_wikipedia    : chr  "https://pt.wikipedia.org/wiki/%C3%81gua_Branca_(Para%C3%ADba)" "https://pt.wikipedia.org/wiki/Aguiar_(Para%C3%ADba)" "https://pt.wikipedia.org/wiki/Alagoa_Grande" "https://pt.wikipedia.org/wiki/Alagoa_Nova" ...
##  $ esfera            : int  1 1 1 1 1 1 1 1 1 1 ...
##  $ cancelled         : logi  FALSE FALSE FALSE FALSE FALSE FALSE ...

Na tabela de localidades também existem 16 colunas, onde temos:

  • id : o id da tabela de localidades
  • uf : a unidade federativa da localidade
  • codigo_uf : o código da unidade federativa
  • mesoregiao : a mesoregião da localidade
  • codigo_mesoregiao : o código da mesoregião
  • microregiao : a microregião da localidade
  • codigo_microregiao: o código da microregião
  • nome : o nome da localidade
  • codigo_ibge : o código do IGBE
  • codigo_siaf : o código do SIAF
  • link_ibge : o link do IBGE
  • bandeira : a bandeira da localidade
  • brasao : o brasão da localidade
  • link_wikipedia : o link da wikipédia referente a localidade
  • esfera : a esfera da localidade
  • cancelled : um boolean que provavelmente indica se algo da localidade foi cancelado

Tabela jurisdicionados

Vejamos agora a tabela de jurisdicionados, referente à chave estrangeira de obras:

jurisdicionado_db2 %>% 
    str()
## 'data.frame':    1048 obs. of  12 variables:
##  $ id                   : num  855 839 877 316 291 ...
##  $ pessoajuridica       : num  855 839 877 316 291 ...
##  $ poder                : int  1 1 1 1 1 1 1 1 1 1 ...
##  $ tipojurisdicionado_fk: num  13 13 2 5 10 13 2 13 10 13 ...
##  $ localidade_fk        : num  5566 5566 5566 5566 5566 ...
##  $ nome                 : chr  "Secretaria de Estado da Receita" "Secretaria de Estado do Desenvolvimento e da Articulação Municipal" "Superintendência de Administração do Meio Ambiente" "Fundação Estadual do Bem Estar do Menor Alice de Almeida" ...
##  $ codigo_sagres        : chr  "0" "0" "0" "0" ...
##  $ previdenciario       : logi  FALSE FALSE FALSE FALSE FALSE FALSE ...
##  $ municipio_importacao : chr  "Paraíba" "Paraíba" "Paraíba" "Paraíba" ...
##  $ tramita_id           : num  855 839 877 316 291 ...
##  $ cancelled            : logi  FALSE FALSE FALSE FALSE FALSE FALSE ...
##  $ esfera               : int  2 2 2 2 2 2 2 2 2 2 ...

Na tabela de jurisdicionados existem 12 colunas, onde temos:

  • id : o id da tabela de jurisdicionados
  • pessoajuridica : provavelmente uma chave estrangeira para a tabela de pessoa jurídica
  • poder : um código que referencia algo ao poder do jurisdicionado
  • tipojurisdicionado_fk: uma chave estrangeira que referencia a tabela de tipojurisdicionado
  • localidade_fk : uma chave estrangeira para a localidade
  • nome : o nome do jurisdicionado
  • codigo_sagres : o código do SAGRES
  • previdenciario : um boolean que indica
  • municipio_importacao : o município da importação
  • tramita_id : o id do tramita
  • cancelled : um boolean que indica que algo relativo ao jurisdicionado foi cancelado
  • esfera : a esfera da localidade

O nome do jurisdicionado parece coincidir com a unidade gestora do banco de dados do SAGRES.

Tabela complexos

Vejamos a tabela de complexos, referente à chave estrangeira de obras:

complexo %>% 
    str()
## 'data.frame':    720 obs. of  5 variables:
##  $ id       : num  160 164 350 435 442 237 290 332 365 366 ...
##  $ descricao: chr  "REFORMA DA FARMACIA" "prefeitura" "Cconstrução de uma creche, tipo 02, na zona urbana do Município de Boa Vista/PB" "CONSTRUÇÃO DE 03 (TRÊS) SALAS PARA ATENDIMENTO À SECRETARIA DE AGRICULTURA" ...
##  $ tipo     : chr  "OUTRAS" "OUTRAS" "CRECHE" "OUTRAS" ...
##  $ codigo   : chr  "1601" "1649" "3505" "4354" ...
##  $ cancelled: logi  FALSE FALSE FALSE FALSE FALSE FALSE ...

Na tabela de complexos existem 5 colunas, onde temos:

  • id : o id da tabela
  • descricao: a descrição do complexo
  • tipo : o tipo do complexo
  • codigo : o código do complexo
  • cancelled: um boolean que indica que algo relativo ao complexo foi cancelado

Podemos ver que a tabela de complexo complementa a de obra, visto que tem o tipo da obra. Além disso, cada complexo é referenciado por mais de uma obra, como podemos ver a seguir pela diferenca entre a chave estrangeira em obras (existem 28681 linhas) e o id em complexos (existem 720 linhas):

obra %>% 
    filter(!is.na(fk_complexo_obras)) %>% 
    nrow()
## [1] 28681
complexo %>% 
    nrow()
## [1] 720

Tabela acompanhamento

Vejamos a tabela de acompanhamento, a que de fato contém os pontos georeferenciados.

acompanhamento %>% 
    str()
## 'data.frame':    56218 obs. of  7 variables:
##  $ id                      : num  1 2 3 4 5 6 7 8 9 10 ...
##  $ tipo                    : int  1 1 1 1 2 2 2 2 1 1 ...
##  $ tipo_georeferenciamento : int  3 2 1 2 2 2 2 2 2 2 ...
##  $ valor_georeferenciamento: chr  "[-6.896274219677315, -38.57133507728577]" "Quadra Coberta Com Vestiário.kml" "[-7.214552, -38.586303]" "PAVIMENTAÇÃO DE RUAS E LADEIRAS.kml" ...
##  $ fk_obra                 : num  8 24 34 22 22 22 22 8 12 59 ...
##  $ cancelled               : logi  FALSE FALSE FALSE FALSE FALSE FALSE ...
##  $ data_cadastro           : Date, format: NA NA ...

Na tabela de acompanhamentos existem 5 colunas, onde temos:

  • id : o id do acompanhamento
  • tipo : o tipo do acompanhamento
  • tipo_georeferenciamento : o tipo do georeferenciamento
  • valor_georeferenciamento: o valor do georeferenciamento
  • fk_obra : a chave estrangeira para a obra
  • cancelled : um boolean que indica que algo relativo ao acompanhamento foi cancelado
  • data_cadastro : a data de cadastro do acompanhamento

Tipos de acompanhamento

Vejamos os tipos de acompanhamento

acompanhamento %>% 
    arrange(valor_georeferenciamento) %>% 
    head() %>% 
    kable()
id tipo tipo_georeferenciamento valor_georeferenciamento fk_obra cancelled data_cadastro
68 2 0 0 340 FALSE 2007-04-02
69 1 0 0 340 FALSE 2007-04-02
70 2 0 0 341 FALSE 2004-02-02
71 1 0 0 341 FALSE 2004-02-02
72 2 0 0 342 FALSE 2007-07-20
73 1 0 0 342 FALSE 2007-07-20 
acompanhamento %>% 
    filter(tipo_georeferenciamento == 1) %>% 
    arrange(valor_georeferenciamento) %>% 
    head() %>% 
    kable()
id tipo tipo_georeferenciamento valor_georeferenciamento fk_obra cancelled data_cadastro
56053 3 1 [0, 0] 16890 FALSE 2017-08-18
56054 3 1 [0, 0] 16915 FALSE 2017-08-18
354 1 1 [ [2.29455010034144 48.8582710828632], [-94.799 38.8556 324.9168], [-1.4894 50.9017 35.9664], [121.6403 25.0618 38.1], [-38.162088, -6.940324 261.834]] 485 FALSE 2012-11-10
364 2 1 [ [2.29455010034144 48.8582710828632], [-94.799 38.8556 324.9168], [-1.4894 50.9017 35.9664], [121.6403 25.0618 38.1], [-38.162088, -6.940324 261.834]] 485 FALSE 2012-11-10
1201 1 1 [ [2.29455010034144 48.8582710828632], [-94.799 38.8556 324.9168], [-1.4894 50.9017 35.9664], [121.6403 25.0618 38.1], [-38.165843, -6.94268 260.7706]] 908 FALSE 2006-03-01
1208 2 1 [ [2.29455010034144 48.8582710828632], [-94.799 38.8556 324.9168], [-1.4894 50.9017 35.9664], [121.6403 25.0618 38.1], [-38.165843, -6.94268 260.7706]] 908 FALSE 2006-03-01 
acompanhamento %>% 
    filter(tipo_georeferenciamento == 2) %>% 
    head() %>% 
    kable()
id tipo tipo_georeferenciamento valor_georeferenciamento fk_obra cancelled data_cadastro
2 1 2 Quadra Coberta Com Vestiário.kml 24 FALSE NA
4 1 2 PAVIMENTAÇÃO DE RUAS E LADEIRAS.kml 22 FALSE NA
5 2 2 PAVIMENTAÇÃO DE RUAS E LADEIRAS.kml 22 FALSE NA
6 2 2 PAVIMENTAÇÃO DE RUAS E LADEIRAS.kml 22 FALSE NA
7 2 2 PAVIMENTAÇÃO DE RUAS E LADEIRAS.kml 22 FALSE NA
8 2 2 UNIDADE DE ACOLHIMENTO.kml 8 FALSE NA 
acompanhamento %>% 
    filter(tipo_georeferenciamento == 3) %>% 
    head() %>% 
    kable()
id tipo tipo_georeferenciamento valor_georeferenciamento fk_obra cancelled data_cadastro
1 1 3 [-6.896274219677315, -38.57133507728577] 8 FALSE NA
13 1 3 [-7.043786383642173, -37.27304399013519] 140 FALSE NA
14 1 3 [-7.893936046154858, -37.118151783943176] 86 FALSE NA
15 1 3 [-7.82752685577032, -37.16200053691864] 144 FALSE NA
16 2 3 [-7.043749116121552, -37.273065447807305] 140 FALSE NA
19 1 3 [-6.890394712764171, -37.72682547569275] 186 FALSE NA

Vemos que a geolocalização pode ser de quatro tipos, onde o 0 é quando não existe georeferenciamento, 1 é uma lista de pares latitude e longitude, o tipo 2 é uma referência para um arquivo KML, o qual não foi encontrado na base de dados, e o tipo 3 é apenas um par de latitude e longitude.

Dados faltantes

Tabela obras

Vejamos os dados faltantes na tabela de obras:

anomalies.obra <- anomalies(obra)
## Warning in anomalies(obra): Found 3 possible problematic variables: 
## cancelled, dimensao, obra_incorporavel_patrimonio
anomalies.obra$variables %>% 
    kable()
Variable q qNA pNA qZero pZero qBlank pBlank qInf pInf qDistinct type anomalous_percent
cancelled 28681 0 - 28424 99.1% 0 - 0 - 2 Logical 99.1%
dimensao 28681 0 - 27925 97.36% 0 - 0 - 616 Numeric 97.36%
obra_incorporavel_patrimonio 28681 0 - 26182 91.29% 0 - 0 - 2 Logical 91.29%
valor_obra 28681 0 - 22676 79.06% 0 - 0 - 4311 Numeric 79.06%
numero_protocolo_tramita 28681 13 0.05% 0 - 0 - 0 - 1265 Character 0.05%
fk_tipo_execucao 28681 0 - 0 - 0 - 0 - 26 Character -
fk_tipo_obra 28681 0 - 0 - 0 - 0 - 37 Integer -
fk_localidade 28681 0 - 0 - 0 - 0 - 221 Numeric -
fk_jurisdicionado 28681 0 - 0 - 0 - 0 - 498 Numeric -
fk_complexo_obras 28681 0 - 0 - 0 - 0 - 720 Numeric -
planilha_contratada 28681 0 - 0 - 0 - 0 - 1244 Character -
numero_contrato 28681 0 - 0 - 0 - 0 - 3665 Character -
numero_obra 28681 0 - 0 - 0 - 0 - 4507 Character -
descricao_localidade 28681 0 - 0 - 0 - 0 - 14346 Character -
descricao_sucinta_obra 28681 0 - 0 - 0 - 0 - 25951 Character -
id 28681 0 - 0 - 0 - 0 - 28681 Numeric - 
anomalies.obra$problem_variables %>% 
    kable()
Variable q qNA pNA qZero pZero qBlank pBlank qInf pInf qDistinct type anomalous_percent problems
cancelled 28681 0 - 28424 99.1% 0 - 0 - 2 Logical 99.1% Anomalies present in 99.1% of the rows.
dimensao 28681 0 - 27925 97.36% 0 - 0 - 616 Numeric 97.36% Anomalies present in 97.36% of the rows.
obra_incorporavel_patrimonio 28681 0 - 26182 91.29% 0 - 0 - 2 Logical 91.29% Anomalies present in 91.29% of the rows.

Vemos que exceto o número do protocolo tramita em que 0.05% das observações são nulas, nenhuma outra coluna tem observações nulas, porém a porcentagem de zeros chega a 97,36% na dimensão e 79,06% no valor da obra. Apesar de acusar 99,1% de zeros na coluna de obras canceladas, é uma coluna com tipo lógico, faz sentido que a maioria das obras não sejam canceladas, o que também acontece na coluna de obras incorporáveis ao patrimônio, onde acusa que 91,29% das obras tem valor zero.

Apesar da maioria das colunas não apresentarem valores nulos ou zeros, muitas delas possuem valores sem significado, por exemplo:

obra %>% 
    group_by(numero_contrato) %>% 
    summarise(quantidade = n()) %>% 
    arrange(-quantidade) %>% 
    head() %>% 
    kable()
numero_contrato quantidade
0000 18567
000332011 39
000052010 34
000912008 33
- 30
000762015 29

Existem 18567 números de contrato 0000, assim como 30 com valor “-”.

Do mesmo modo, a coluna referente à descrição sucinta da obra tem alguns valores não nulos, mas sem significado:

obras.sem.descricao <- obra %>% 
    filter(str_count(descricao_sucinta_obra, "[:alpha:]|[:blank:]") / nchar(descricao_sucinta_obra) < 0.6)

obras.sem.descricao %>% 
    select(id, fk_jurisdicionado, numero_contrato, numero_obra, descricao_sucinta_obra, fk_localidade) %>% 
    head() %>% 
    kable()
id fk_jurisdicionado numero_contrato numero_obra descricao_sucinta_obra fk_localidade
4223 616 0000 00102011 1 1254
5798 627 0000 00142003 - 1285
8443 642 0000 00112002 - 1245
8639 645 0000 00122003 - 1269
5794 627 0000 00132003 - 1285
369 31 0000 00022003 - 1369 
(obras.sem.descricao %>% count()) / (obra %>% count()) * 100
##         n
## 1 1.55155

Onde existem 1.5% de dados com mais de 40% da palavra composta por caracteres diferente do alfabeto.

Da mesma forma, a descrição da localidade tem vários valores sem significado:

obras.sem.localidade <- obra %>% 
    filter(str_count(descricao_localidade, "[:alpha:]|[:blank:]") / nchar(descricao_localidade) < 0.6)

obras.sem.localidade %>% 
    select(id, fk_jurisdicionado, numero_contrato, numero_obra, descricao_sucinta_obra, fk_localidade) %>% 
    head() %>% 
    kable()
id fk_jurisdicionado numero_contrato numero_obra descricao_sucinta_obra fk_localidade
5798 627 0000 00142003 - 1285
8443 642 0000 00112002 - 1245
8639 645 0000 00122003 - 1269
5794 627 0000 00132003 - 1285
369 31 0000 00022003 - 1369
370 32 0000 00012003 - 1379 
(obras.sem.localidade %>% count()) / (obra %>% count()) * 100
##          n
## 1 1.589903

É possível ver que 1,58% das obras tem localidade composta por mais de 40% de caracteres diferente do alfabeto.

Tabela jurisdicionados

Vejamos os dados faltantes de jurisdicionados:

anomalies.jurisdicionado <- anomalies(jurisdicionado_db2)
## Warning in anomalies(jurisdicionado_db2): Found 2 possible problematic variables: 
## previdenciario, cancelled
anomalies.jurisdicionado$variables %>% 
    kable()
Variable q qNA pNA qZero pZero qBlank pBlank qInf pInf qDistinct type anomalous_percent
previdenciario 1048 0 - 975 93.03% 0 - 0 - 2 Logical 93.03%
cancelled 1048 0 - 955 91.13% 0 - 0 - 2 Logical 91.13%
codigo_sagres 1048 0 - 296 28.24% 0 - 0 - 685 Character 28.24%
esfera 1048 102 9.73% 0 - 0 - 0 - 4 Integer 9.73%
localidade_fk 1048 8 0.76% 0 - 0 - 0 - 230 Numeric 0.76%
municipio_importacao 1048 4 0.38% 0 - 0 - 0 - 230 Character 0.38%
poder 1048 0 - 0 - 0 - 0 - 5 Integer -
tipojurisdicionado_fk 1048 0 - 0 - 0 - 0 - 13 Numeric -
nome 1048 0 - 0 - 0 - 0 - 1022 Character -
id 1048 0 - 0 - 0 - 0 - 1048 Numeric -
pessoajuridica 1048 0 - 0 - 0 - 0 - 1048 Numeric -
tramita_id 1048 0 - 0 - 0 - 0 - 1048 Numeric - 
anomalies.jurisdicionado$problem_variables %>% 
    kable()
Variable q qNA pNA qZero pZero qBlank pBlank qInf pInf qDistinct type anomalous_percent problems
previdenciario 1048 0 - 975 93.03% 0 - 0 - 2 Logical 93.03% Anomalies present in 93.03% of the rows.
cancelled 1048 0 - 955 91.13% 0 - 0 - 2 Logical 91.13% Anomalies present in 91.13% of the rows.

Vemos que em 28,24% dos dados, o código do sagres é vazio e em 9,73% dos dados da esfera, 0,76% da chave estrangeira da localidade e 0,38% do município de importação são nulos. Da mesma forma que o anterior, as colunas previdenciario e cancelled são do tipo boolean, logo, faz sentido a maior parte deles terem apenas um valor.

Tabela localidade

Vejamos agora os dados das localidades:

anomalies.localidade <- anomalies(localidade)
## Warning in anomalies(localidade): Found 7 possible problematic variables: 
## cancelled, codigo_ibge, esfera, bandeira, brasao, link_ibge, link_wikipedia
anomalies.localidade$variables %>% 
    kable()
Variable q qNA pNA qZero pZero qBlank pBlank qInf pInf qDistinct type anomalous_percent
cancelled 5566 0 - 5566 100% 0 - 0 - 1 Logical 100%
codigo_ibge 5566 5342 95.98% 1 0.02% 0 - 0 - 225 Integer 95.99%
esfera 5566 5342 95.98% 0 - 0 - 0 - 2 Integer 95.98%
bandeira 5566 5342 95.98% 0 - 0 - 0 - 122 Character 95.98%
brasao 5566 5342 95.98% 0 - 0 - 0 - 193 Character 95.98%
link_ibge 5566 5342 95.98% 0 - 0 - 0 - 225 Character 95.98%
link_wikipedia 5566 5342 95.98% 0 - 0 - 0 - 225 Character 95.98%
codigo_siaf 5566 0 - 68 1.22% 0 - 0 - 5498 Integer 1.22%
codigo_uf 5566 0 - 1 0.02% 0 - 0 - 28 Integer 0.02%
codigo_mesoregiao 5566 0 - 1 0.02% 0 - 0 - 138 Integer 0.02%
codigo_microregiao 5566 0 - 1 0.02% 0 - 0 - 559 Integer 0.02%
uf 5566 0 - 0 - 0 - 0 - 28 Character -
mesoregiao 5566 0 - 0 - 0 - 0 - 138 Character -
microregiao 5566 0 - 0 - 0 - 0 - 555 Character -
nome 5566 0 - 0 - 0 - 0 - 5293 Character -
id 5566 0 - 0 - 0 - 0 - 5566 Numeric - 
anomalies.localidade$problem_variables %>% 
    kable()
Variable q qNA pNA qZero pZero qBlank pBlank qInf pInf qDistinct type anomalous_percent problems
cancelled 5566 0 - 5566 100% 0 - 0 - 1 Logical 100% Anomalies present in 100% of the rows. Less than 2 distinct values.
codigo_ibge 5566 5342 95.98% 1 0.02% 0 - 0 - 225 Integer 95.99% Anomalies present in 95.99% of the rows.
esfera 5566 5342 95.98% 0 - 0 - 0 - 2 Integer 95.98% Anomalies present in 95.98% of the rows.
bandeira 5566 5342 95.98% 0 - 0 - 0 - 122 Character 95.98% Anomalies present in 95.98% of the rows.
brasao 5566 5342 95.98% 0 - 0 - 0 - 193 Character 95.98% Anomalies present in 95.98% of the rows.
link_ibge 5566 5342 95.98% 0 - 0 - 0 - 225 Character 95.98% Anomalies present in 95.98% of the rows.
link_wikipedia 5566 5342 95.98% 0 - 0 - 0 - 225 Character 95.98% Anomalies present in 95.98% of the rows.

Vemos que várias colunas da localidade tem 95,98% dos valores faltantes, como codigo_ibge, esfera, bandeira, brasao, link_ibge e link_wikipedia.

Tabela complexo

Agora pelo complexo:

anomalies.complexo <- anomalies(complexo)
## Warning in anomalies(complexo): Found 1 possible problematic variables: 
## cancelled
anomalies.complexo$variables %>% 
    kable()
Variable q qNA pNA qZero pZero qBlank pBlank qInf pInf qDistinct type anomalous_percent
cancelled 720 0 - 720 100% 0 - 0 - 1 Logical 100%
tipo 720 0 - 0 - 0 - 0 - 32 Character -
descricao 720 0 - 0 - 0 - 0 - 694 Character -
id 720 0 - 0 - 0 - 0 - 720 Numeric -
codigo 720 0 - 0 - 0 - 0 - 720 Character - 
anomalies.complexo$problem_variables %>% 
    kable()
Variable q qNA pNA qZero pZero qBlank pBlank qInf pInf qDistinct type anomalous_percent problems
cancelled 720 0 - 720 100% 0 - 0 - 1 Logical 100% Anomalies present in 100% of the rows. Less than 2 distinct values.

Vemos que os dados de complexo parecem ser bem completos, não tendo dados faltantes, visto que a coluna cancelled é um boolean, onde nenhum complexo foi cancelado. Apesar disso, duas descrições não fazem sentido:

complexo %>% 
    filter(str_count(descricao, "[:alpha:]|[:blank:]") / nchar(descricao) < 0.6) %>% 
    kable()
id descricao tipo codigo cancelled
506 12412341234 HOSPITAL 506-5061 FALSE
505 21342134 AÇUDE BARRAGEM 505-5057 FALSE

Além disso, é possível ver que várias obras referenciam um mesmo complexo. Podemos ver a seguir os complexos com mais obras:

obra %>% 
    group_by(fk_complexo_obras) %>% 
    summarise(quantidade = n()) %>% 
    arrange(-quantidade) %>% 
    head() %>% 
    left_join(complexo, by = c("fk_complexo_obras" = "id")) %>% 
    kable()
fk_complexo_obras quantidade descricao tipo codigo cancelled
298 27920 IMPORTAÇÃO GEOPB OUTRAS 2982 FALSE
137 8 CONSTRUÇÃO RE DE ÁGUA, REDE ESGOTO SANITÁRIO, REPOSIÇÃO DE CALÇAMENTO E PINTURA DE MEIO FIO REDE COLETORA DE ESGOTO 1376 FALSE
272 4 Reposição de Pavimentação PAVIMENTAÇÃO PARALEPÍPEDO 2723 FALSE
676 4 Sistema Integrado de Esgotamento Sanitário de João Pessoa OUTRAS 676-6761 FALSE
37 3 REFORMA DE ESCOLAS OUTRAS 370 FALSE
81 3 RECONSTRUÇÃO E REFORMA GINÁSIO POLIESPORTIVO / QUADRA DE ESPORTES 811 FALSE

Vemos que o complexo com mais obras é fictício, visto que a descrição é IMPORTAÇÃO GEOPB.

Tabela acompanhamento

Por último, vejamos os dados faltantes da tabela de acompanhamento:

anomalies.acompanhamento <- anomalies(acompanhamento)
## Warning in anomalies(acompanhamento): Found 3 possible problematic variables: 
## cancelled, tipo_georeferenciamento, valor_georeferenciamento
anomalies.acompanhamento$variables %>% 
    kable()
Variable q qNA pNA qZero pZero qBlank pBlank qInf pInf qDistinct type anomalous_percent
cancelled 56218 0 - 56218 100% 0 - 0 - 1 Logical 100%
tipo_georeferenciamento 56218 0 - 55598 98.9% 0 - 0 - 4 Integer 98.9%
valor_georeferenciamento 56218 0 - 55598 98.9% 0 - 0 - 457 Character 98.9%
data_cadastro 56218 120 0.21% 0 - 0 - 0 - 3919 Date 0.21%
tipo 56218 0 - 0 - 0 - 0 - 3 Integer -
fk_obra 56218 0 - 0 - 0 - 0 - 28145 Numeric -
id 56218 0 - 0 - 0 - 0 - 56218 Numeric - 
anomalies.acompanhamento$problem_variables %>% 
    kable()
Variable q qNA pNA qZero pZero qBlank pBlank qInf pInf qDistinct type anomalous_percent problems
cancelled 56218 0 - 56218 100% 0 - 0 - 1 Logical 100% Anomalies present in 100% of the rows. Less than 2 distinct values.
tipo_georeferenciamento 56218 0 - 55598 98.9% 0 - 0 - 4 Integer 98.9% Anomalies present in 98.9% of the rows.
valor_georeferenciamento 56218 0 - 55598 98.9% 0 - 0 - 457 Character 98.9% Anomalies present in 98.9% of the rows.

Vemos que tanto o tipo do georeferenciamento, quanto o valor do mesmo em 98,9% das linhas tem valor igual a zero.

Obras com valores distoantes

Faremos agora uma análise sobre as obras que possuem valores distoantes das demais. Para isso vamos visualizar alguns gráficos para tentar entender melhor onde podem estar esses valores extremos.

Valor total das obras por mesorregião

Podemos ver que, de acordo com os dados, a mesorregião da Borborema é a que mais gasta com obras, com um gasto total de mais de 1 bilhão e 250 milhões de reais. Isso chama bastante a atenção pois era de se esperar que a mesorregião da Mata Paraibana concentrasse a maior parte dos gastos uma vez que esta contém a capital do estado e sua região metropolitana.

######################################PLOTANDO OS FRÁFICOS######################################

obras_por_mesoregiao %>%
    filter(total_obras != 0) %>%
    mutate(total_obras = total_obras/ UM_MILHAO) %>%
    ggplot(aes(x = reorder(mesoregiao, -total_obras), y = total_obras)) + 
    geom_col() + 
    labs(title = "Gastos com obras por mesorregião", x="Mesorregião", y = "Valor gasto (milhões de R$)")

Valor total das obras por microrregião

Agora veremos os gastos por microrregião para granularizar melhor as informações. De acordo com os dados a microrregião do Seridó Oriental Paraibano realizou gastos de mais de um bilhão de reais em obras, o que é no minímo alarmante pois essa microrregião possui uma população pequena e ainda assim gastou mais em obras do que a microrregião de João Pessoa que compreende a capital do estado e cidades populosas como Santa Rita e Bayeux.

obras_por_microregiao %>%
    filter(total_obras != 0) %>%
    mutate(total_obras = total_obras/ UM_MILHAO) %>%
    ggplot(aes(x = reorder(microregiao, -total_obras), y = total_obras)) + 
    geom_col() + 
    labs(title = "Gastos com obras por microrregião", x="Microrregião", y = "Valor gasto (milhões de R$)") + 
    coord_flip()

Municípios que mais gastam

Tendo em vista que a microrregião do Seridó Oriental é a que mais gasta, veremos agora os 20 municípios que mais gastam em obras para tentar identificar gastos que possam ter contribuido para o valor tão elevado visto anteriormente. O município de Cubati (que possui 8000 habitantes segundo o senso do IBGE de 2016) da mesorregião do Seridó Oriental realizou, segundo os dados, gastos superiores a 1 bilhão de reais, o que nos leva a entender que este município é responsável pelos valores extremos vistos anteriormente.

obras_por_municipio_top20 %>%
    filter(total_obras != 0) %>%
    mutate(total_obras = total_obras/ UM_MILHAO) %>%
    ggplot(aes(x = reorder(municipio, -total_obras), y = total_obras)) + 
    geom_col() + 
    labs(title = "Top 20 dos municípios que mais gastam", x="Município", y = "Valor gasto (milhões de R$)") + 
    coord_flip()

Orgãos públicos que mais gastam

A prefeitura de Cubati e o orgão que mais gasta, como era de devido ao gráfico anterior. A prefeitura do Conde também parece gastar além da conta, tendo gastos superiores a orgãos “maiores” como a Prefeitura de João Pessoa e a Prefeitura de Campina Grande.

obras_por_orgao_top20 %>%
    filter(total_obras != 0) %>%
    mutate(total_obras = total_obras/ UM_MILHAO) %>%
    ggplot(aes(x = reorder(nome, -total_obras), y = total_obras)) + 
    geom_col() + 
    labs(title = "Top 20 dos orgãos públicos que mais gastam", x="Orgão", y = "Valor gasto (milhões de R$)") + 
    coord_flip()

Municípios com maior quantidade de obras

Nesse gráfico temos os municípios que mais realizaram obras, se não levarmos em conta as obras que possuem o município “Não informado” (que podem ser obras de qualquer município) temos um resultado interessante, o município de Manaíra (com população de 11.000 habitantes segundo o censo do IBGE de 2016) tem uma quantidade de obras maior do que municípios como Cabedelo (67.000 habitantes segundo o censo do IBGE de 2016).

obras_por_orgao_quantidade_top20 %>%
    ggplot(aes(x = reorder(municipio, -freq), y = freq)) + 
    geom_col() + 
    coord_flip() +
    labs(title = "Top 20 dos municípios com maior quantidade de obras", x="Município", y = "Número de obras")

Separando as obras com valor extremo

Agora que já temos uma noção de onde podem estar as obras com valores muito altos vamos utilziar boxplotes para tentar separá-las para posteriormente visualizá-las separadamente.

knitr::opts_chunk$set(fig.width=12, fig.height=8) 
plotly::plot_ly(
    join_obras_jurisdicionado %>% filter(valor_obra > UM_MILHAO) %>% group_by(mesoregiao),
    x = ~mesoregiao,
    y = ~log(valor_obra),
    color = ~mesoregiao,
    type = "box",
    boxpoints = "suspectedoutliers"
) %>%
    plotly::layout(title = "Obras com valores extremos (considerando apenas obras \ncom valor maior que um milhão)", xaxis=list(title="Mesorregião"), yaxis=list(title="Valor da obra em escala logarítimica"))

Obras mais caras do estado

Graças ao gráfico acima, sabemos quais obras distoam das outras feitas em sua mesorregião e podemos mostrá-las em um gráfico interativo. No gráfico abaixo podemos ver essas obras e informações sobre elas como descrição, valor, mediana das obras da região e etc. Vejamos:

knitr::opts_chunk$set(fig.width=10, fig.height=8) 
outliers_ggplot <- 
    outliers %>% 
    mutate(mediana_meso = lapply(mesoregiao, getMedianaMeso), mediana_micro = lapply(microregiao, getMedianaMicro)) %>%
    ggplot(aes(y = valor_obra/UM_MILHAO, x = municipio, color = mesoregiao, 
               text = paste0("<b>Orgão: </b>", nome,
                             "<br><b>Valor:</b> R$", format(valor_obra/UM_MILHAO, digits = 2), " milhões", 
                             "<br><b>Mediana da mesoregião: </b>R$", mediana_meso, " milhões",
                             "<br><b>Mediana da microrregião: </b>R$", mediana_micro, " milhões",
                             "<br><b>Local da obra: </b>", breakString(descricao_localidade), 
                             "<b>Descrição: </b>", breakString(descricao_sucinta_obra)))) + 
    geom_point(alpha = .7) + 
    coord_flip() +
    labs(title = "Obras com valores extremos", x="Município", y = "Valor da obra em milhões de R$")
    

plotly::ggplotly(outliers_ggplot, tooltip=c("text"))

Conclusão

Após uma análise sobre os dados relativos à obras, destacamos alguns pontos importantes:

  • Existem duas tabelas de jurisdicionado, uma na base vigia e outra na base cabobranco, porém só a da base cabobranco que contém dados.
  • Tabela obra:
    • Esta tabela tem referência para outras tabelas, como as colunas fk_tipo_obra, fk_tipo_execucao e planilha_contratada, porém não encontramos as tabelas que são apontadas por essas referências.
    • Esta tabela tem 0.05% das observações são nulas na coluna número do protocolo tramita, a porcentagem de zeros chega a 97,36% na dimensão e 79,06% no valor da obra.
    • Esta tabela tem 18567 números de contrato 0000, assim como 30 com valor “-”.
    • Esta tabela tem 1.5% das descrições com mais de 40% composta por caracteres diferente do alfabeto.
    • Esta tabela tem 1,58% das localidades com mais de 40% composta por caracteres diferente do alfabeto.
  • Tabela jurisdicionado:
    • Esta tabela tem 28,24% do código do sagres com valor zero e em 9,73% dos dados da esfera, 0,76% da chave estrangeira da localidade e 0,38% do município de importação são nulos.
  • Tabela localidade:
    • Algumas colunas da localidade tem 95,98% dos valores faltantes, como codigo_ibge, esfera, bandeira, brasao, link_ibge e link_wikipedia.
  • Tabela complexo:
    • Dois complexos se destacaram das demais, por terem descrição “12412341234” e “21342134”
    • 27920 complexos tem descrição “IMPORTAÇÃO GEOPB”.
  • Tabela acompanhamento:
    • O tipo do georeferenciamento e o valor do mesmo em 98,9% das linhas tem valor igual a zero.
  • Valores extremos:
    • Nós acreditamos que a obra mais cara encontrada (da prefeitura de Cubati) pode ser fruto de algum erro de digitação na hora da entrada dos dados. É necessário verificar isso.
    • A segunda obra mais cara (da prefeitura do Conde) também parece ser fruto de erro.

Algumas destas observações são características de dados legados, onde existem colunas cujos valores tem significados especiais, porém identificamos os mesmos para entender como lidar com estas características do banco de dados.